
 ALTER     PROC sp_ct_mkg_load_forecast_details(
 
 @SALES_PERSON VARCHAR(50),
 @CUSTOMER VARCHAR(100),
 @PRODUCT VARCHAR(100),
 @MONTH_FROM INT,
 @MONTH_TO INT,
 @YEAR INT,
 @ENDUSER VARCHAR(100) ) -- added by varma 11 sep 2004
 
 AS
 BEGIN
 
 
 	IF RTRIM(@MONTH_FROM) = -1
 	BEGIN
 		SET @MONTH_FROM = 0
 	END
 
 	IF RTRIM(@MONTH_TO) = -1
 	BEGIN
 		SET @MONTH_TO = 11
 	END
 
 	IF @CUSTOMER IS NULL OR RTRIM(@CUSTOMER) = ''
 	BEGIN
 		SET @CUSTOMER = ''
 	END
 
 	DECLARE @YEAR_TO INT
 	SET @YEAR_TO = @YEAR + @MONTH_TO/12
 	SET @MONTH_TO = @MONTH_TO%12
 
 	IF @SALES_PERSON IS NOT NULL AND RTRIM(@SALES_PERSON) <> ''
 	BEGIN
 		IF @CUSTOMER = '-1'
 		BEGIN
 			SELECT SF_ID[ID], SALES_PERSON[USER], CUSTOMER, PRODUCT, SF_MONTH[MONTH],
 			MONTH_NAME = CASE SF_MONTH
 			         WHEN 0  THEN 'Jan ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 1  THEN 'Feb ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 2  THEN 'Mar ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 3  THEN 'Apr ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 4  THEN 'May ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 5  THEN 'Jun ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 6  THEN 'Jul ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 7  THEN 'Aug ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 8  THEN 'Sep ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 9  THEN 'Oct ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 10 THEN 'Nov ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 11 THEN 'Dec ' + CONVERT(VARCHAR, SF_YEAR)
 			      END,
 			SF_YEAR[YEAR], SF_QTY[QUANTITY], SF_UNIT_PRICE[UNIT_PRICE], CONVERT(VARCHAR, CREATED_DT, 101)[CREATED_DATE], CONVERT(VARCHAR, MODIFIED_DT, 101)[MODIFIED_DATE], SF_CONFIDENCE_LEVEL [CONFIDENCE_LEVEL],END_USER [ENDUSER]
 			FROM SALES_FORECAST_MASTER
 			WHERE SALES_PERSON = @SALES_PERSON 
 			AND PRODUCT = @PRODUCT
 			AND ( ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR ) OR
 			      ( SF_MONTH <= @MONTH_TO AND SF_YEAR = @YEAR_TO ) OR
 			      ( SF_YEAR < @YEAR_TO AND SF_YEAR > @YEAR ) )
 		END
 		ELSE 
 		BEGIN		
 				SELECT SF_ID[ID], SALES_PERSON[USER], CUSTOMER, PRODUCT, SF_MONTH[MONTH],
	 			MONTH_NAME = CASE SF_MONTH
	 			         WHEN 0  THEN 'Jan ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 1  THEN 'Feb ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 2  THEN 'Mar ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 3  THEN 'Apr ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 4  THEN 'May ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 5  THEN 'Jun ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 6  THEN 'Jul ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 7  THEN 'Aug ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 8  THEN 'Sep ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 9  THEN 'Oct ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 10 THEN 'Nov ' + CONVERT(VARCHAR, SF_YEAR)
	 			         WHEN 11 THEN 'Dec ' + CONVERT(VARCHAR, SF_YEAR)
	 			      END,
	 			SF_YEAR[YEAR], SF_QTY[QUANTITY], SF_UNIT_PRICE[UNIT_PRICE], CONVERT(VARCHAR, CREATED_DT, 101)[CREATED_DATE], CONVERT(VARCHAR, MODIFIED_DT, 101)[MODIFIED_DATE],SF_CONFIDENCE_LEVEL [CONFIDENCE_LEVEL],END_USER [ENDUSER]
	 			FROM SALES_FORECAST_MASTER
	 			WHERE SALES_PERSON = @SALES_PERSON 
	 			AND ( ( CUSTOMER = @CUSTOMER ) OR ( @CUSTOMER = '' ) )
	 			AND PRODUCT = @PRODUCT
	 			AND ( ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR ) OR
	 			      ( SF_MONTH <= @MONTH_TO AND SF_YEAR = @YEAR_TO ) OR
	 			      ( SF_YEAR < @YEAR_TO AND SF_YEAR > @YEAR ) )
				AND END_USER = @ENDUSER
		END
 	END
 	ELSE
 	BEGIN
 		IF @CUSTOMER = '-1'
 		BEGIN	
 			SELECT SF_ID[ID], SALES_PERSON[USER], CUSTOMER, PRODUCT, SF_MONTH[MONTH],
 			MONTH_NAME = CASE SF_MONTH
 			         WHEN 0  THEN 'Jan ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 1  THEN 'Feb ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 2  THEN 'Mar ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 3  THEN 'Apr ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 4  THEN 'May ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 5  THEN 'Jun ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 6  THEN 'Jul ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 7  THEN 'Aug ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 8  THEN 'Sep ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 9  THEN 'Oct ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 10 THEN 'Nov ' + CONVERT(VARCHAR, SF_YEAR)
 			         WHEN 11 THEN 'Dec ' + CONVERT(VARCHAR, SF_YEAR)
 			      END,
 			SF_YEAR[YEAR], SF_QTY[QUANTITY], SF_UNIT_PRICE[UNIT_PRICE], CONVERT(VARCHAR, CREATED_DT, 101)[CREATED_DATE], CONVERT(VARCHAR, MODIFIED_DT, 101)[MODIFIED_DATE],SF_CONFIDENCE_LEVEL [CONFIDENCE_LEVEL],END_USER [ENDUSER]
 			FROM SALES_FORECAST_MASTER
 			WHERE PRODUCT = @PRODUCT
 			AND ( ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR ) OR
 			      ( SF_MONTH <= @MONTH_TO AND SF_YEAR = @YEAR_TO ) OR
 			      ( SF_YEAR < @YEAR_TO AND SF_YEAR > @YEAR ) )
 			ORDER BY SALES_PERSON
 		END
 		ELSE
 		BEGIN	--Modified by varma 
			IF @ENDUSER IS NOT NULL 
			BEGIN
				SELECT SF_ID[ID], SALES_PERSON[USER], CUSTOMER, PRODUCT, SF_MONTH[MONTH],
		 		MONTH_NAME = CASE SF_MONTH
		 		         WHEN 0  THEN 'Jan ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 1  THEN 'Feb ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 2  THEN 'Mar ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 3  THEN 'Apr ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 4  THEN 'May ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 5  THEN 'Jun ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 6  THEN 'Jul ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 7  THEN 'Aug ' + CONVERT(VARCHAR, SF_YEAR)
		 			 WHEN 8  THEN 'Sep ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 9  THEN 'Oct ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 10 THEN 'Nov ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 11 THEN 'Dec ' + CONVERT(VARCHAR, SF_YEAR)
		 		         END,
		 		SF_YEAR[YEAR], SF_QTY[QUANTITY], SF_UNIT_PRICE[UNIT_PRICE], CONVERT(VARCHAR, CREATED_DT, 101)[CREATED_DATE], CONVERT(VARCHAR, MODIFIED_DT, 101)[MODIFIED_DATE], SF_CONFIDENCE_LEVEL [CONFIDENCE_LEVEL],END_USER [ENDUSER]
		 		FROM SALES_FORECAST_MASTER
		 		WHERE ( ( CUSTOMER = @CUSTOMER ) OR ( @CUSTOMER = '' ) )
		 		AND PRODUCT = @PRODUCT
		 		AND ( ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR ) OR
		 		      ( SF_MONTH <= @MONTH_TO AND SF_YEAR = @YEAR_TO ) OR
		 		      ( SF_YEAR < @YEAR_TO AND SF_YEAR > @YEAR ) )
				AND END_USER = @ENDUSER
				ORDER BY SALES_PERSON
			END
			ELSE
			BEGIN
				SELECT SF_ID[ID], SALES_PERSON[USER], CUSTOMER, PRODUCT, SF_MONTH[MONTH],
		 		MONTH_NAME = CASE SF_MONTH
		 		         WHEN 0  THEN 'Jan ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 1  THEN 'Feb ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 2  THEN 'Mar ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 3  THEN 'Apr ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 4  THEN 'May ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 5  THEN 'Jun ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 6  THEN 'Jul ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 7  THEN 'Aug ' + CONVERT(VARCHAR, SF_YEAR)
		 			 WHEN 8  THEN 'Sep ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 9  THEN 'Oct ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 10 THEN 'Nov ' + CONVERT(VARCHAR, SF_YEAR)
		 		         WHEN 11 THEN 'Dec ' + CONVERT(VARCHAR, SF_YEAR)
		 		         END,
		 		SF_YEAR[YEAR], SF_QTY[QUANTITY], SF_UNIT_PRICE[UNIT_PRICE], CONVERT(VARCHAR, CREATED_DT, 101)[CREATED_DATE], CONVERT(VARCHAR, MODIFIED_DT, 101)[MODIFIED_DATE], SF_CONFIDENCE_LEVEL [CONFIDENCE_LEVEL],END_USER [ENDUSER]
		 		FROM SALES_FORECAST_MASTER
		 		WHERE ( ( CUSTOMER = @CUSTOMER ) OR ( @CUSTOMER = '' ) )
		 		AND PRODUCT = @PRODUCT
		 		AND ( ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR ) OR
		 		      ( SF_MONTH <= @MONTH_TO AND SF_YEAR = @YEAR_TO ) OR
		 		      ( SF_YEAR < @YEAR_TO AND SF_YEAR > @YEAR ) )
				ORDER BY SALES_PERSON
			END
 		END
 	END
 
 END
